package com.spi.soconversion.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.spi.soconversion.Batch;
import com.spi.soconversion.base.SOCException;
import com.spi.soconversion.util.ConversionType;
import com.spi.soconversion.util.ErrorConstants;
import com.spi.soconversion.util.ErrorMessageReader;
import com.spi.soconversion.util.QueryConstants;
import com.spi.soconversion.util.QueryReader;
import com.spi.soconversion.util.ResourceProvider;
import com.spi.soconversion.util.SOCConstants;
import com.spi.soconversion.util.SOCUtility;

public class BatchDAOImpl implements BatchDAO {
	
	private static final String TOTAL_RECORDS_PROCESSED = "TOTAL_RECORDS_PROCESSED";

	private static final String TOTAL_COUNT = "TOTAL_COUNT";

	private static final String BATCH_NUMBER = "BATCH_NUMBER";

	private Connection d_Connection;
	
    static private org.apache.commons.logging.Log log = 
        org.apache.commons.logging.LogFactory.getLog(BatchDAOImpl.class);

	private ResourceProvider provider = ResourceProvider.getInstance();
	
	public BatchDAOImpl() {

	}	

    /**
     * This Method gets the total records to be processed from TBL_STG_OPEN_SALES_ORDER. 

     * @throws SOCException - wraps all the exceptions that occur while processing
     */
	public int getTotalRecordsToBeProcessed() throws SOCException {
    	int result = 0;
    	String totalRecQuery = QueryReader.getQuery(QueryConstants.OSO_GET_TOTAL_RECORDS_TO_BE_PROCESSED_QUERY);
    	Statement stmt=null;
    	ResultSet rsInfo=null;
    	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_TOTAL_RECORDS_TO_BE_PROCESSED_STARTS_OSO));
    	
    	try {
    		d_Connection = provider.getdbConnection();
    		stmt=d_Connection.createStatement();
    		rsInfo=stmt.executeQuery(totalRecQuery);
			if(rsInfo!=null) {
				rsInfo.next();
				result= rsInfo.getInt(TOTAL_COUNT);				
			}			
			
			try {
				if (rsInfo != null) {
					rsInfo.close();
				}
				
				if(stmt!=null) {
					stmt.close();
				}			
			}
			catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		catch(SQLException sqle) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_FETCH_TOTAL_RECORDS)+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_FETCH_TOTAL_RECORDS) ,sqle);    		
		}	
		log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_TOTAL_RECORDS_TO_BE_PROCESSED_ENDS_OSO));
		return result;
	}
	
	/**
     * This Method gets the total records processed. 
     * @throws SOCException - wraps all the exceptions that occur while processing
     */
	public int getTotalRecordsProcessed(int batchNum, String conversionType) throws SOCException {
    	int result = 0;
    	String batchInfoQuery = QueryReader.getQuery(QueryConstants.OSO_GET_TOTAL_RECORDS_PROCESSED);
    	PreparedStatement batchStmt=null;
    	ResultSet rsBatchInfo=null;
    	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_TOTAL_RECORDS_PROCESSED_STARTS));
    	try {
    		d_Connection = provider.getdbConnection();
    		batchStmt = d_Connection.prepareStatement(batchInfoQuery);
    		batchStmt.setString(1, conversionType);
    		batchStmt.setInt(2, batchNum);
    		rsBatchInfo=batchStmt.executeQuery();
			if(rsBatchInfo!=null) {
				rsBatchInfo.next();
				result= rsBatchInfo.getInt(TOTAL_RECORDS_PROCESSED);				
			}			
			
			try {
				if (rsBatchInfo != null) {
					rsBatchInfo.close();
				}
				
				if(batchStmt!=null) {
					batchStmt.close();
				}			
			}
			catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		catch(SQLException sqle) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_FETCH_BATCH_INFO_FOR_TOTAL_RECORDS)+ConversionType.OPEN_SALES_ORDER.getDisplayText()+SOCConstants.BLANK_STRING+batchNum+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_FETCH_BATCH_INFO_FOR_TOTAL_RECORDS) + ConversionType.OPEN_SALES_ORDER.getDisplayText()+SOCConstants.BLANK_STRING+batchNum,sqle);    		
		}	
		log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_TOTAL_RECORDS_PROCESSED_ENDS));
		return result;
	}	
	
	/**
     * This Method gets the batch number. 
	 * @throws SOCException 
     * @throws SOCException - wraps all the exceptions that occur while processing
     */
	public int getBatchNumber(String type) throws SOCException {

    	int result = 0;
    	String batchInfoQuery = QueryReader.getQuery(QueryConstants.OSO_GET_BATCH_NUMBER_QUERY);
    	PreparedStatement batchStmt=null;
    	ResultSet rsBatchInfo=null;

    	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_BATCH_NUMBER_STARTS));
    	try {
    		d_Connection = provider.getdbConnection();
    		batchStmt= d_Connection.prepareStatement(batchInfoQuery);
    		batchStmt.setString(1, type);
    		rsBatchInfo=batchStmt.executeQuery();
			if(rsBatchInfo!=null) {
				rsBatchInfo.next();
				result = rsBatchInfo.getInt(BATCH_NUMBER);				
			}
			try {
				if (rsBatchInfo != null) {
					rsBatchInfo.close();
				}
				
				if(batchStmt!=null) {
					batchStmt.close();
				}			
			}
			catch (SQLException sqle) {
				sqle.printStackTrace();
			}			
		}
		catch(SQLException sqle) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_FETCH_BATCH_NUMBER)+ConversionType.OPEN_SALES_ORDER.getDisplayText()+SOCConstants.NEW_LINE+sqle.getMessage()));
			sqle.printStackTrace();
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_FETCH_BATCH_NUMBER)+ConversionType.OPEN_SALES_ORDER.getDisplayText() ,sqle);    		
		}	
		log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_RETRIEVE_BATCH_NUMBER_ENDS));
		return result;
	}
	
	/**
     * This Method updates the batch information for the current batch. 
     * @throws SOCException - wraps all the exceptions that occur while processing
     */
	public void updateBatch(Batch batch) throws SOCException {
		log.info("Updating the batch information...");
    	String batchUpdateQuery = QueryReader.getQuery(QueryConstants.OSO_INSERT_CONVERSION_BATCH_RECORDS_QUERY);
    	PreparedStatement batchStmt=null;
    	int recordCount=0;
    	try {
    		d_Connection = provider.getdbConnection();
    		batchStmt=d_Connection.prepareStatement(batchUpdateQuery);
    		batchStmt.setString(1, ConversionType.OPEN_SALES_ORDER.getDisplayText());
    		batchStmt.setInt(2, batch.getBatchNumber());
    		if(batch.getBatchSize() > 0) {
    			batchStmt.setInt(3, batch.getBatchSize());
        	}
    		else {
    			batchStmt.setInt(3, batch.getFetchSize());
    		}
    		batchStmt.setInt(4, batch.getProcessedSize());
    		batchStmt.setInt(5, batch.getDataIssuesSize());
    		batchStmt.setLong(6, batch.getRecordsFailedValidation());
    		batchStmt.setTimestamp(7, batch.getStartTime());
    		batchStmt.setTimestamp(8, batch.getEndTime());
    		log.info("Executing the update query for batch...");
    		recordCount=batchStmt.executeUpdate();
    		if(recordCount==0) {
				log.info(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
	            				ErrorConstants.NO_RECORD_POPULATION_CONVERSION_BATCH_RECORDS)));
				throw new SOCException(
	            		ErrorMessageReader.getError(
	            				ErrorConstants.NO_RECORD_POPULATION_CONVERSION_BATCH_RECORDS));
			}
    		log.info("Completed updating the batch information...");

    		if(batchStmt!=null) {
				batchStmt.close();
			}			
		}
		catch(SQLException sqle) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_UPDATE_BATCH_INFO)+SOCConstants.NEW_LINE+sqle.getMessage()));
			sqle.printStackTrace();
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_UPDATE_BATCH_INFO) ,sqle);    		
		}			
	}	
}